using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using PropertyManagement.Core.Model;

namespace PropertyManagement.Core.DataAccess {
        public static partial class PropertyManagementFeeBillDataAccess {
             
            public static PropertyManagementFeeBill GetById(int id){
                var sql = @"select * from dbo.PropertyManagementFeeBill where PropertyManagementFeeBillID = @id ";
                var paramList = new[] {
                    new SqlParameter("id", id)
                };
                using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    if (iReader.Read( )) {
                            return CreationPropertyManagementFeeBillByIReader(iReader);
                    };
                    return null;
                }
            }

            public static void Add(PropertyManagementFeeBill entity){
                var sql = @"insert into dbo.PropertyManagementFeeBill values (@houseid,@billtype,@callablemoney,@paidmoney,@effectivedate,@expiredate,@overduefee,@paiddate,@iscompletepaid,@creationdate,@lastupdate,@creationuserid,@lastactionuserid) ;   select SCOPE_IDENTITY (); ";
                  var paramList = new SqlParameter[] {
							new SqlParameter("billtype",entity.BillType),
							new SqlParameter("callablemoney",entity.CallableMoney),
							new SqlParameter("creationdate",entity.CreationDate),
							new SqlParameter("creationuserid",entity.CreationUserID),
							new SqlParameter("effectivedate",entity.EffectiveDate),
							new SqlParameter("expiredate",entity.ExpireDate),
							new SqlParameter("houseid",entity.HouseID),
							new SqlParameter("iscompletepaid",entity.IsCompletePaid),
							new SqlParameter("lastactionuserid",entity.LastActionUserID),
							new SqlParameter("lastupdate",entity.LastUpdate),
							new SqlParameter("overduefee",entity.OverdueFee),
							new SqlParameter("paiddate",entity.PaidDate),
							new SqlParameter("paidmoney",entity.PaidMoney)
                        };
                  using ( var ireader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                            paramList)) {
                        if (ireader.Read()) {
                            entity.PropertyManagementFeeBillID = int.Parse(ireader[0].ToString());
                        }
                  }
            
            }   

            public static void Remove(PropertyManagementFeeBill entity) {
                var sql = @"delete from dbo.PropertyManagementFeeBill where PropertyManagementFeeBillID = @id";
                var paramList = new[] {
                    new SqlParameter("id", entity.PropertyManagementFeeBillID)
                };
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList);
            }   

            public static IEnumerable<PropertyManagementFeeBill> LoadAll(){
                    var sql = " select * from dbo.PropertyManagementFeeBill ";
                    var list = new List<PropertyManagementFeeBill>( );
                    using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql)) {
                        while (iReader.Read( )) {
                                list.Add(CreationPropertyManagementFeeBillByIReader(iReader));
                        };
                    }  
                    return list;
            }   
        
            public static void Update(PropertyManagementFeeBill entity){
                var sql = @" update dbo.PropertyManagementFeeBill set BillType=@billtype,CallableMoney=@callablemoney,CreationDate=@creationdate,CreationUserID=@creationuserid,EffectiveDate=@effectivedate,ExpireDate=@expiredate,HouseID=@houseid,IsCompletePaid=@iscompletepaid,LastActionUserID=@lastactionuserid,LastUpdate=@lastupdate,OverdueFee=@overduefee,PaidDate=@paiddate,PaidMoney=@paidmoney where PropertyManagementFeeBillID = @id ";
                var paramList = new SqlParameter[] {
                            new SqlParameter("id",entity.PropertyManagementFeeBillID),
							new SqlParameter("billtype",entity.BillType),
							new SqlParameter("callablemoney",entity.CallableMoney),
							new SqlParameter("creationdate",entity.CreationDate),
							new SqlParameter("creationuserid",entity.CreationUserID),
							new SqlParameter("effectivedate",entity.EffectiveDate),
							new SqlParameter("expiredate",entity.ExpireDate),
							new SqlParameter("houseid",entity.HouseID),
							new SqlParameter("iscompletepaid",entity.IsCompletePaid),
							new SqlParameter("lastactionuserid",entity.LastActionUserID),
							new SqlParameter("lastupdate",entity.LastUpdate),
							new SqlParameter("overduefee",entity.OverdueFee),
							new SqlParameter("paiddate",entity.PaidDate),
							new SqlParameter("paidmoney",entity.PaidMoney)
                        };
                SqlServerHelper.ExecuteNonQuery(SqlServerHelper.ConnectionString, CommandType.Text, sql, paramList);
            }
            
            public static bool Exists(PropertyManagementFeeBill entity){
                return entity.PropertyManagementFeeBillID != 0 && GetById(entity.PropertyManagementFeeBillID)!=null;
            }


            private static PropertyManagementFeeBill CreationPropertyManagementFeeBillByIReader(IDataReader iReader){
                if(iReader!=null){
                      return new PropertyManagementFeeBill{
							BillType = iReader["BillType"] is DBNull ? default(BillType) : (BillType)iReader["BillType"] ,
							CallableMoney = iReader["CallableMoney"] is DBNull ? default(Decimal) : (Decimal)iReader["CallableMoney"] ,
							CreationDate = iReader["CreationDate"] is DBNull ? default(DateTime) : (DateTime)iReader["CreationDate"] ,
							CreationUserID = iReader["CreationUserID"] is DBNull ? default(Int32) : (Int32)iReader["CreationUserID"] ,
							EffectiveDate = iReader["EffectiveDate"] is DBNull ? default(DateTime) : (DateTime)iReader["EffectiveDate"] ,
							ExpireDate = iReader["ExpireDate"] is DBNull ? default(DateTime) : (DateTime)iReader["ExpireDate"] ,
							HouseID = iReader["HouseID"] is DBNull ? default(Int32) : (Int32)iReader["HouseID"] ,
							IsCompletePaid = iReader["IsCompletePaid"] is DBNull ? default(Boolean) : (Boolean)iReader["IsCompletePaid"] ,
							LastActionUserID = iReader["LastActionUserID"] is DBNull ? default(Int32) : (Int32)iReader["LastActionUserID"] ,
							LastUpdate = iReader["LastUpdate"] is DBNull ? default(DateTime) : (DateTime)iReader["LastUpdate"] ,
							OverdueFee = iReader["OverdueFee"] is DBNull ? default(Decimal) : (Decimal)iReader["OverdueFee"] ,
							PaidDate = iReader["PaidDate"] is DBNull ? default(DateTime) : (DateTime)iReader["PaidDate"] ,
							PaidMoney = iReader["PaidMoney"] is DBNull ? default(Decimal) : (Decimal)iReader["PaidMoney"] ,
							PropertyManagementFeeBillID = iReader["PropertyManagementFeeBillID"] is DBNull ? default(Int32) : (Int32)iReader["PropertyManagementFeeBillID"] 
                        };
                }
                return null; 
            }

            
            
            public static PropertyManagementFeeBill GetByPKs(PropertyManagementFeeBill entity){
                var sql =  @"select *  from dbo.PropertyManagementFeeBill where HouseID = @houseid and BillType = @billtype and EffectiveDate = @effectivedate ";
                var paramList = new SqlParameter[] {
							new SqlParameter("houseid",entity.HouseID),
							new SqlParameter("billtype",entity.BillType),
							new SqlParameter("effectivedate",entity.EffectiveDate)
                        };
                 using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    if (iReader.Read( )) {
                            return CreationPropertyManagementFeeBillByIReader(iReader);
                    };
                    return null;
                }
            }

            public static bool ExistsByPKs(PropertyManagementFeeBill entity){
                var sql =  @"select *  from dbo.PropertyManagementFeeBill where HouseID = @houseid and BillType = @billtype and EffectiveDate = @effectivedate ";
                var paramList = new SqlParameter[] {
							new SqlParameter("houseid",entity.HouseID),
							new SqlParameter("billtype",entity.BillType),
							new SqlParameter("effectivedate",entity.EffectiveDate)
                        };
                 using (var iReader = SqlServerHelper.ExecuteReader(SqlServerHelper.ConnectionString, CommandType.Text, sql,
                                                                paramList)) {
                    return iReader.Read( );
                }
            }

    }
}